{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Reshaping Data\n",
    "\n",
    "## About the data\n",
    "In this notebook, we will using daily temperature data from the [National Centers for Environmental Information (NCEI) API](https://www.ncdc.noaa.gov/cdo-web/webservices/v2). We will use the Global Historical Climatology Network - Daily (GHCND) data set; see the documentation [here](https://www1.ncdc.noaa.gov/pub/data/cdo/documentation/GHCND_documentation.pdf).\n",
    "\n",
    "This data was collected for New York City for October 2018, using the Boonton 1 station (GHCND:USC00280907). It contains:\n",
    "- the daily minimum temperature (`TMIN`)\n",
    "- the daily maximum temperature (`TMAX`)\n",
    "- the daily temperature at time of observation (`TOBS`)\n",
    "\n",
    "*Note: The NCEI is part of the National Oceanic and Atmospheric Administration (NOAA) and, as you can see from the URL for the API, this resource was created when the NCEI was called the NCDC. Should the URL for this resource change in the future, you can search for the NCEI weather API to find the updated one.*\n",
    "\n",
    "## Setup\n",
    "We need to import `pandas` and read in the long-format data to get started:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>datatype</th>\n",
       "      <th>date</th>\n",
       "      <th>temp_C</th>\n",
       "      <th>temp_F</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>TMAX</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>21.1</td>\n",
       "      <td>69.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>TMIN</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>8.9</td>\n",
       "      <td>48.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>TOBS</td>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>TMAX</td>\n",
       "      <td>2018-10-02</td>\n",
       "      <td>23.9</td>\n",
       "      <td>75.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>TMIN</td>\n",
       "      <td>2018-10-02</td>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  datatype       date  temp_C  temp_F\n",
       "0     TMAX 2018-10-01    21.1   69.98\n",
       "1     TMIN 2018-10-01     8.9   48.02\n",
       "2     TOBS 2018-10-01    13.9   57.02\n",
       "3     TMAX 2018-10-02    23.9   75.02\n",
       "4     TMIN 2018-10-02    13.9   57.02"
      ]
     },
     "execution_count": 1,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "\n",
    "long_df = pd.read_csv(\n",
    "    'data/long_data.csv', \n",
    "    usecols=['date', 'datatype', 'value']\n",
    ").rename(\n",
    "    columns={\n",
    "        'value' : 'temp_C'\n",
    "    }\n",
    ").assign(\n",
    "    date=lambda x: pd.to_datetime(x.date),\n",
    "    temp_F=lambda x: (x.temp_C * 9/5) + 32\n",
    ")\n",
    "long_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Transposing\n",
    "Transposing swaps the rows and the columns. We use the `T` attribute to do so:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>datatype</th>\n",
       "      <td>TMAX</td>\n",
       "      <td>TMIN</td>\n",
       "      <td>TOBS</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>TMIN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <td>2018-10-01 00:00:00</td>\n",
       "      <td>2018-10-01 00:00:00</td>\n",
       "      <td>2018-10-01 00:00:00</td>\n",
       "      <td>2018-10-02 00:00:00</td>\n",
       "      <td>2018-10-02 00:00:00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>temp_C</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>temp_F</th>\n",
       "      <td>69.98</td>\n",
       "      <td>48.02</td>\n",
       "      <td>57.02</td>\n",
       "      <td>75.02</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                            0                    1                    2  \\\n",
       "datatype                 TMAX                 TMIN                 TOBS   \n",
       "date      2018-10-01 00:00:00  2018-10-01 00:00:00  2018-10-01 00:00:00   \n",
       "temp_C                   21.1                  8.9                 13.9   \n",
       "temp_F                  69.98                48.02                57.02   \n",
       "\n",
       "                            3                    4  \n",
       "datatype                 TMAX                 TMIN  \n",
       "date      2018-10-02 00:00:00  2018-10-02 00:00:00  \n",
       "temp_C                   23.9                 13.9  \n",
       "temp_F                  75.02                57.02  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "long_df.head().T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Pivoting\n",
    "Going from long to wide format.\n",
    "\n",
    "### `pivot()`\n",
    "We can restructure our data by picking a column to go in the index (`index`), a column whose unique values will become column names (`columns`), and the values to place in those columns (`values`). The `pivot()` method can be used when we don't need to perform any aggregation in addition to our restructuring (when our index is unique); if this is not the case, we need the `pivot_table()` method which we will cover in [chapter 4](https://github.com/stefmolin/Hands-On-Data-Analysis-with-Pandas/tree/master/ch_04). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>datatype</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "datatype    TMAX  TMIN  TOBS\n",
       "date                        \n",
       "2018-10-01  21.1   8.9  13.9\n",
       "2018-10-02  23.9  13.9  17.2\n",
       "2018-10-03  25.0  15.6  16.1\n",
       "2018-10-04  22.8  11.7  11.7\n",
       "2018-10-05  23.3  11.7  18.9"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_df = long_df.pivot(\n",
    "    index='date', columns='datatype', values='temp_C'\n",
    ")\n",
    "pivoted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Note there is also the `pd.pivot()` function which yields equivalent results:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>datatype</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "datatype    TMAX  TMIN  TOBS\n",
       "date                        \n",
       "2018-10-01  21.1   8.9  13.9\n",
       "2018-10-02  23.9  13.9  17.2\n",
       "2018-10-03  25.0  15.6  16.1\n",
       "2018-10-04  22.8  11.7  11.7\n",
       "2018-10-05  23.3  11.7  18.9"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.pivot(\n",
    "    index=long_df.date, columns=long_df.datatype, values=long_df.temp_C\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now that the data is pivoted, we have wide-format data that we can grab summary statistics with:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>datatype</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>31.000000</td>\n",
       "      <td>31.000000</td>\n",
       "      <td>31.000000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>16.829032</td>\n",
       "      <td>7.561290</td>\n",
       "      <td>10.022581</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>std</th>\n",
       "      <td>5.714962</td>\n",
       "      <td>6.513252</td>\n",
       "      <td>6.596550</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>min</th>\n",
       "      <td>7.800000</td>\n",
       "      <td>-1.100000</td>\n",
       "      <td>-1.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25%</th>\n",
       "      <td>12.750000</td>\n",
       "      <td>2.500000</td>\n",
       "      <td>5.550000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>50%</th>\n",
       "      <td>16.100000</td>\n",
       "      <td>6.700000</td>\n",
       "      <td>8.300000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75%</th>\n",
       "      <td>21.950000</td>\n",
       "      <td>13.600000</td>\n",
       "      <td>16.100000</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>max</th>\n",
       "      <td>26.700000</td>\n",
       "      <td>17.800000</td>\n",
       "      <td>21.700000</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "datatype       TMAX       TMIN       TOBS\n",
       "count     31.000000  31.000000  31.000000\n",
       "mean      16.829032   7.561290  10.022581\n",
       "std        5.714962   6.513252   6.596550\n",
       "min        7.800000  -1.100000  -1.100000\n",
       "25%       12.750000   2.500000   5.550000\n",
       "50%       16.100000   6.700000   8.300000\n",
       "75%       21.950000  13.600000  16.100000\n",
       "max       26.700000  17.800000  21.700000"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_df.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can also provide multiple values to pivot on, which will result in a hierarchical index:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">temp_C</th>\n",
       "      <th colspan=\"3\" halign=\"left\">temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datatype</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>69.98</td>\n",
       "      <td>48.02</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "      <td>75.02</td>\n",
       "      <td>57.02</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "      <td>77.00</td>\n",
       "      <td>60.08</td>\n",
       "      <td>60.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "      <td>73.04</td>\n",
       "      <td>53.06</td>\n",
       "      <td>53.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "      <td>73.94</td>\n",
       "      <td>53.06</td>\n",
       "      <td>66.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           temp_C             temp_F              \n",
       "datatype     TMAX  TMIN  TOBS   TMAX   TMIN   TOBS\n",
       "date                                              \n",
       "2018-10-01   21.1   8.9  13.9  69.98  48.02  57.02\n",
       "2018-10-02   23.9  13.9  17.2  75.02  57.02  62.96\n",
       "2018-10-03   25.0  15.6  16.1  77.00  60.08  60.98\n",
       "2018-10-04   22.8  11.7  11.7  73.04  53.06  53.06\n",
       "2018-10-05   23.3  11.7  18.9  73.94  53.06  66.02"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_df = long_df.pivot(\n",
    "    index='date', columns='datatype', values=['temp_C', 'temp_F']\n",
    ")\n",
    "pivoted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the hierarchical index, if we want to select `TMIN` in Fahrenheit, we will first need to select `'temp_F'` and then `'TMIN'`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date\n",
       "2018-10-01    48.02\n",
       "2018-10-02    57.02\n",
       "2018-10-03    60.08\n",
       "2018-10-04    53.06\n",
       "2018-10-05    53.06\n",
       "Name: TMIN, dtype: float64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pivoted_df['temp_F']['TMIN'].head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `unstack()`"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We have been working with a single index throughout this chapter; however, we can create an index from any number of columns with `set_index()`. This gives us a `MultiIndex` where the outermost level corresponds to the first element in the list provided to `set_index()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[[2018-10-01 00:00:00, 2018-10-02 00:00:00, 2018-10-03 00:00:00, 2018-10-04 00:00:00, 2018-10-05 00:00:00, 2018-10-06 00:00:00, 2018-10-07 00:00:00, 2018-10-08 00:00:00, 2018-10-09 00:00:00, 2018-10-10 00:00:00, 2018-10-11 00:00:00, 2018-10-12 00:00:00, 2018-10-13 00:00:00, 2018-10-14 00:00:00, 2018-10-15 00:00:00, 2018-10-16 00:00:00, 2018-10-17 00:00:00, 2018-10-18 00:00:00, 2018-10-19 00:00:00, 2018-10-20 00:00:00, 2018-10-21 00:00:00, 2018-10-22 00:00:00, 2018-10-23 00:00:00, 2018-10-24 00:00:00, 2018-10-25 00:00:00, 2018-10-26 00:00:00, 2018-10-27 00:00:00, 2018-10-28 00:00:00, 2018-10-29 00:00:00, 2018-10-30 00:00:00, 2018-10-31 00:00:00], ['TMAX', 'TMIN', 'TOBS']],\n",
       "           labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8, 8, 8, 9, 9, 9, 10, 10, 10, 11, 11, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22, 22, 22, 23, 23, 23, 24, 24, 24, 25, 25, 25, 26, 26, 26, 27, 27, 27, 28, 28, 28, 29, 29, 29, 30, 30, 30], [0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2]],\n",
       "           names=['date', 'datatype'])"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multi_index_df = long_df.set_index(['date', 'datatype'])\n",
    "multi_index_df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Notice there are now 2 index sections of the dataframe:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>temp_C</th>\n",
       "      <th>temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2018-10-01</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>21.1</td>\n",
       "      <td>69.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>8.9</td>\n",
       "      <td>48.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOBS</th>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2018-10-02</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>23.9</td>\n",
       "      <td>75.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     temp_C  temp_F\n",
       "date       datatype                \n",
       "2018-10-01 TMAX        21.1   69.98\n",
       "           TMIN         8.9   48.02\n",
       "           TOBS        13.9   57.02\n",
       "2018-10-02 TMAX        23.9   75.02\n",
       "           TMIN        13.9   57.02"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "multi_index_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "With the `MultiIndex`, we can no longer use `pivot()`. We must now use `unstack()`, which by default moves the innermost index onto the columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"3\" halign=\"left\">temp_C</th>\n",
       "      <th colspan=\"3\" halign=\"left\">temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datatype</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>69.98</td>\n",
       "      <td>48.02</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "      <td>75.02</td>\n",
       "      <td>57.02</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "      <td>77.00</td>\n",
       "      <td>60.08</td>\n",
       "      <td>60.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "      <td>73.04</td>\n",
       "      <td>53.06</td>\n",
       "      <td>53.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "      <td>73.94</td>\n",
       "      <td>53.06</td>\n",
       "      <td>66.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           temp_C             temp_F              \n",
       "datatype     TMAX  TMIN  TOBS   TMAX   TMIN   TOBS\n",
       "date                                              \n",
       "2018-10-01   21.1   8.9  13.9  69.98  48.02  57.02\n",
       "2018-10-02   23.9  13.9  17.2  75.02  57.02  62.96\n",
       "2018-10-03   25.0  15.6  16.1  77.00  60.08  60.98\n",
       "2018-10-04   22.8  11.7  11.7  73.04  53.06  53.06\n",
       "2018-10-05   23.3  11.7  18.9  73.94  53.06  66.02"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "unstacked_df = multi_index_df.unstack()\n",
    "unstacked_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "The `unstack()` method also provides the `fill_value` parameter, which let's us fill-in any `NaN` values that might arise from this restructuring of the data. Consider the case that we have data for the average temperature on October 1, 2018, but no other date:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>temp_C</th>\n",
       "      <th>temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th>datatype</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">2018-10-01</th>\n",
       "      <th>TAVG</th>\n",
       "      <td>10.0</td>\n",
       "      <td>50.00</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMAX</th>\n",
       "      <td>21.1</td>\n",
       "      <td>69.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>8.9</td>\n",
       "      <td>48.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOBS</th>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2018-10-02</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>23.9</td>\n",
       "      <td>75.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>13.9</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOBS</th>\n",
       "      <td>17.2</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>25.0</td>\n",
       "      <td>77.00</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                     temp_C  temp_F\n",
       "date       datatype                \n",
       "2018-10-01 TAVG        10.0   50.00\n",
       "           TMAX        21.1   69.98\n",
       "           TMIN         8.9   48.02\n",
       "           TOBS        13.9   57.02\n",
       "2018-10-02 TMAX        23.9   75.02\n",
       "           TMIN        13.9   57.02\n",
       "           TOBS        17.2   62.96\n",
       "2018-10-03 TMAX        25.0   77.00"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extra_data = long_df.append(\n",
    "    [{'datatype' : 'TAVG', 'date': '2018-10-01', 'temp_C': 10, 'temp_F': 50}]\n",
    ").set_index(['date', 'datatype']).sort_index()\n",
    "\n",
    "extra_data.head(8)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "If we use `unstack()` in this case, we will have `NaN` for the `TAVG` columns every day but October 1, 2018:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">temp_C</th>\n",
       "      <th colspan=\"4\" halign=\"left\">temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datatype</th>\n",
       "      <th>TAVG</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>TAVG</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>10.0</td>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>50.0</td>\n",
       "      <td>69.98</td>\n",
       "      <td>48.02</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>NaN</td>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>75.02</td>\n",
       "      <td>57.02</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>NaN</td>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "      <td>NaN</td>\n",
       "      <td>77.00</td>\n",
       "      <td>60.08</td>\n",
       "      <td>60.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>NaN</td>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.04</td>\n",
       "      <td>53.06</td>\n",
       "      <td>53.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>NaN</td>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>73.94</td>\n",
       "      <td>53.06</td>\n",
       "      <td>66.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           temp_C                   temp_F                     \n",
       "datatype     TAVG  TMAX  TMIN  TOBS   TAVG   TMAX   TMIN   TOBS\n",
       "date                                                           \n",
       "2018-10-01   10.0  21.1   8.9  13.9   50.0  69.98  48.02  57.02\n",
       "2018-10-02    NaN  23.9  13.9  17.2    NaN  75.02  57.02  62.96\n",
       "2018-10-03    NaN  25.0  15.6  16.1    NaN  77.00  60.08  60.98\n",
       "2018-10-04    NaN  22.8  11.7  11.7    NaN  73.04  53.06  53.06\n",
       "2018-10-05    NaN  23.3  11.7  18.9    NaN  73.94  53.06  66.02"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extra_data.unstack().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "To address this, we can pass in an appropriate `fill_value`. However, we are restricted to passing in a value for this, not a strategy (like we saw with `fillna()`), so while `-40` is definitely not be the best value, we can use it to illustrate how this works, since this is the temperature at which Fahrenheit and Celsius are equal:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr th {\n",
       "        text-align: left;\n",
       "    }\n",
       "\n",
       "    .dataframe thead tr:last-of-type th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <th colspan=\"4\" halign=\"left\">temp_C</th>\n",
       "      <th colspan=\"4\" halign=\"left\">temp_F</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>datatype</th>\n",
       "      <th>TAVG</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "      <th>TAVG</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>10.0</td>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>50.0</td>\n",
       "      <td>69.98</td>\n",
       "      <td>48.02</td>\n",
       "      <td>57.02</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>-40.0</td>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>75.02</td>\n",
       "      <td>57.02</td>\n",
       "      <td>62.96</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>-40.0</td>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>77.00</td>\n",
       "      <td>60.08</td>\n",
       "      <td>60.98</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>-40.0</td>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>73.04</td>\n",
       "      <td>53.06</td>\n",
       "      <td>53.06</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>-40.0</td>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "      <td>-40.0</td>\n",
       "      <td>73.94</td>\n",
       "      <td>53.06</td>\n",
       "      <td>66.02</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           temp_C                   temp_F                     \n",
       "datatype     TAVG  TMAX  TMIN  TOBS   TAVG   TMAX   TMIN   TOBS\n",
       "date                                                           \n",
       "2018-10-01   10.0  21.1   8.9  13.9   50.0  69.98  48.02  57.02\n",
       "2018-10-02  -40.0  23.9  13.9  17.2  -40.0  75.02  57.02  62.96\n",
       "2018-10-03  -40.0  25.0  15.6  16.1  -40.0  77.00  60.08  60.98\n",
       "2018-10-04  -40.0  22.8  11.7  11.7  -40.0  73.04  53.06  53.06\n",
       "2018-10-05  -40.0  23.3  11.7  18.9  -40.0  73.94  53.06  66.02"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "extra_data.unstack(fill_value=-40).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Melting\n",
    "Going from wide to long format.\n",
    "\n",
    "### Setup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-10-02</td>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-10-03</td>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-10-05</td>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date  TMAX  TMIN  TOBS\n",
       "0  2018-10-01  21.1   8.9  13.9\n",
       "1  2018-10-02  23.9  13.9  17.2\n",
       "2  2018-10-03  25.0  15.6  16.1\n",
       "3  2018-10-04  22.8  11.7  11.7\n",
       "4  2018-10-05  23.3  11.7  18.9"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wide_df = pd.read_csv('data/wide_data.csv')\n",
    "wide_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `melt()`\n",
    "In order to go from wide format to long format, we use the `melt()` method. We have to specify:\n",
    "- which column contains the unique identifier for each row (`date`, here) to `id_vars`\n",
    "- the column(s) that contain the values (`TMAX`, `TMIN`, and `TOBS`, here) to `value_vars`\n",
    "\n",
    "Optionally, we can also provide:\n",
    "- `value_name`: what to call the column that will contain all the values once melted\n",
    "- `var_name`: what to call the column that will contain the names of the variables being measured"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>measurement</th>\n",
       "      <th>temp_C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>21.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-10-02</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>23.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-10-03</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-10-05</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>23.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date measurement  temp_C\n",
       "0  2018-10-01        TMAX    21.1\n",
       "1  2018-10-02        TMAX    23.9\n",
       "2  2018-10-03        TMAX    25.0\n",
       "3  2018-10-04        TMAX    22.8\n",
       "4  2018-10-05        TMAX    23.3"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "melted_df = wide_df.melt(\n",
    "    id_vars='date',\n",
    "    value_vars=['TMAX', 'TMIN', 'TOBS'],\n",
    "    value_name='temp_C',\n",
    "    var_name='measurement'\n",
    ")\n",
    "melted_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Just as we also had `pd.pivot()` there is a `pd.melt()`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>date</th>\n",
       "      <th>measurement</th>\n",
       "      <th>temp_C</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2018-10-01</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>21.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2018-10-02</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>23.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2018-10-03</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>25.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2018-10-04</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>22.8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2018-10-05</td>\n",
       "      <td>TMAX</td>\n",
       "      <td>23.3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         date measurement  temp_C\n",
       "0  2018-10-01        TMAX    21.1\n",
       "1  2018-10-02        TMAX    23.9\n",
       "2  2018-10-03        TMAX    25.0\n",
       "3  2018-10-04        TMAX    22.8\n",
       "4  2018-10-05        TMAX    23.3"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.melt(\n",
    "    wide_df,\n",
    "    id_vars='date',\n",
    "    value_vars=['TMAX', 'TMIN', 'TOBS'],\n",
    "    value_name='temp_C',\n",
    "    var_name='measurement'\n",
    ").head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### `stack()`\n",
    "Another option is `stack()` which will pivot the columns of the dataframe into the innermost level of a `MultiIndex`. To illustrate this, let's set our index to be the `date` column:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>TOBS</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2018-10-01</th>\n",
       "      <td>21.1</td>\n",
       "      <td>8.9</td>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-02</th>\n",
       "      <td>23.9</td>\n",
       "      <td>13.9</td>\n",
       "      <td>17.2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-03</th>\n",
       "      <td>25.0</td>\n",
       "      <td>15.6</td>\n",
       "      <td>16.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-04</th>\n",
       "      <td>22.8</td>\n",
       "      <td>11.7</td>\n",
       "      <td>11.7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2018-10-05</th>\n",
       "      <td>23.3</td>\n",
       "      <td>11.7</td>\n",
       "      <td>18.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            TMAX  TMIN  TOBS\n",
       "date                        \n",
       "2018-10-01  21.1   8.9  13.9\n",
       "2018-10-02  23.9  13.9  17.2\n",
       "2018-10-03  25.0  15.6  16.1\n",
       "2018-10-04  22.8  11.7  11.7\n",
       "2018-10-05  23.3  11.7  18.9"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "wide_df.set_index('date', inplace=True)\n",
    "wide_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "By running `stack()` now, we will create a second level in our index which will contain the column names of our dataframe (`TMAX`, `TMIN`, `TOBS`). This will leave us with a `Series` containing the values:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "date            \n",
       "2018-10-01  TMAX    21.1\n",
       "            TMIN     8.9\n",
       "            TOBS    13.9\n",
       "2018-10-02  TMAX    23.9\n",
       "            TMIN    13.9\n",
       "dtype: float64"
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_series = wide_df.stack()\n",
    "stacked_series.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use the `to_frame()` method on our `Series` object to turn it into a `DataFrame`. Since the series doesn't have a name at the moment, we will pass in the name as an argument:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th>values</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"3\" valign=\"top\">2018-10-01</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>21.1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>8.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TOBS</th>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"2\" valign=\"top\">2018-10-02</th>\n",
       "      <th>TMAX</th>\n",
       "      <td>23.9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TMIN</th>\n",
       "      <td>13.9</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 values\n",
       "date                   \n",
       "2018-10-01 TMAX    21.1\n",
       "           TMIN     8.9\n",
       "           TOBS    13.9\n",
       "2018-10-02 TMAX    23.9\n",
       "           TMIN    13.9"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_df = stacked_series.to_frame('values')\n",
    "stacked_df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Once again, we have a `MultiIndex`:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "MultiIndex(levels=[['2018-10-01', '2018-10-02', '2018-10-03', '2018-10-04', '2018-10-05', '2018-10-06', '2018-10-07', '2018-10-08', '2018-10-09', '2018-10-10', '2018-10-11', '2018-10-12', '2018-10-13', '2018-10-14', '2018-10-15', '2018-10-16', '2018-10-17', '2018-10-18', '2018-10-19', '2018-10-20', '2018-10-21', '2018-10-22', '2018-10-23', '2018-10-24', '2018-10-25', '2018-10-26', '2018-10-27', '2018-10-28', '2018-10-29', '2018-10-30', '2018-10-31'], ['TMAX', 'TMIN', 'TOBS']],\n",
       "           labels=[[0, 0, 0, 1, 1, 1, 2, 2, 2, 3, 3, 3, 4, 4, 4, 5, 5, 5, 6, 6, 6, 7, 7, 7, 8, 8, 8, 9, 9, 9, 10, 10, 10, 11, 11, 11, 12, 12, 12, 13, 13, 13, 14, 14, 14, 15, 15, 15, 16, 16, 16, 17, 17, 17, 18, 18, 18, 19, 19, 19, 20, 20, 20, 21, 21, 21, 22, 22, 22, 23, 23, 23, 24, 24, 24, 25, 25, 25, 26, 26, 26, 27, 27, 27, 28, 28, 28, 29, 29, 29, 30, 30, 30], [0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2, 0, 1, 2]],\n",
       "           names=['date', None])"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Unfortunately, we don't have a name for the `datatype` level:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FrozenList(['date', None])"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_df.index.names"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "We can use `rename()` to address this though:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "FrozenList(['date', 'datatype'])"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "stacked_df.index.rename(['date', 'datatype'], inplace=True)\n",
    "stacked_df.index.names"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}
